/**
 * 
 */
package rer.persistencia;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import rer.model.Responsaveis;
import rer.util.ConexaoJDBC_mysql;

/**
 * @author Henrique Serrat
 * @since 15/11/2014
 *
 */


public class ResponsaveisDAO implements IResponsaveisDAO {

	private Statement statement;
	private PreparedStatement preparedStatement;
	private ResultSet resultSet;
	private String query;
	private Responsaveis responsavel;
	private List<Responsaveis> responsaveis;
	private Connection conexao = ConexaoJDBC_mysql.getConnection();
	
	
	/* (non-Javadoc)
	 * @see rer.persistencia.IResponsaveisDAO#cadastrar(rer.model.Responsaveis)
	 */
	@Override
	public Boolean cadastrar(Responsaveis resp) {
		// TODO Auto-generated method stub
		try{
			
			query = "INSERT INTO `responsaveis`(`nomeResp`, `cpfResp`, `telResp`, "
					+ "`tel2Resp`, `celResp`, `Aluno_idAluno`)"
					+ " VALUES (?,?,?,?,?,?,?)";
			
			preparedStatement = conexao.prepareStatement(query);
			preparedStatement.setString(1, responsavel.getNomeResp());
			preparedStatement.setString(2, responsavel.getCpfResp());
			preparedStatement.setString(3, responsavel.getTelResp());
			preparedStatement.setString(4, responsavel.getTel2Resp());
			preparedStatement.setString(5, responsavel.getCelResp());
			preparedStatement.setInt(6, responsavel.getIdAluno());
			//l
			preparedStatement.execute();
			
			return true;
			
		}catch(SQLException erro){
			System.out.println(erro.getMessage());
		}finally {
			
			try{
				preparedStatement.close();
				//System.out.println("\nStatement fechado");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				System.out.println("\n Erro ao fechar o objeto statement.");
			}
		}
		
		return false;
	}
	/* (non-Javadoc)
	 * @see rer.persistencia.IResponsaveisDAO#editar(int)
	 */
	@Override
	public void editar(Responsaveis resp) {
		// TODO Auto-generated method stub
		
		try{
			
			query = "UPDATE `responsaveis` SET `nomeResp`=?,`cpfResp`=?,"
					+ "`telResp`=?,`tel2Resp`=?,"
					+ "`celResp`=?,`Aluno_idAluno`=? WHERE `idResp` = ?";
			
			preparedStatement = conexao.prepareStatement(query);
			preparedStatement.setString(1, responsavel.getNomeResp());
			preparedStatement.setString(2, responsavel.getCpfResp());
			preparedStatement.setString(3, responsavel.getTelResp());
			preparedStatement.setString(4, responsavel.getTel2Resp());
			preparedStatement.setString(5, responsavel.getCelResp());
			preparedStatement.setInt(6, responsavel.getIdAluno());
			preparedStatement.setInt(7, responsavel.getIdResp());
			
			preparedStatement.execute();
			
			
		}catch(SQLException erro){
			System.out.println(erro.getMessage());
		}finally {
			
			try{
				preparedStatement.close();
				//System.out.println("\nStatement fechado");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				System.out.println("\n Erro ao fechar o objeto statement.");
			}
		}
		
	}
	/* (non-Javadoc)
	 * @see rer.persistencia.IResponsaveisDAO#deletar(int)
	 */
	@Override
	public Boolean deletar(int id) {
		// TODO Auto-generated method stub
		try{
			
			query = "DELETE FROM `db_ceirr`.`responsaveis` WHERE `responsaveis`.`idResp` = ?";
			
			preparedStatement = conexao.prepareStatement(query);
			preparedStatement.setInt(1, id);
			
			preparedStatement.executeUpdate();
			
			System.out.println("\nTarefa apagada com sucesso");
			
			return true;
			
		}catch(SQLException erro){
			System.out.println(erro.getMessage());
			
			return false;
		}
	}
	/* (non-Javadoc)
	 * @see rer.persistencia.IResponsaveisDAO#listar()
	 */
	@Override
	public List<Responsaveis> listar() {
		// TODO Auto-generated method stub
		responsavel = null;
		
		try{
			
			query = "SELECT * FROM `responsaveis` WHERE 1";
			statement = conexao.createStatement();
			resultSet = statement.executeQuery(query);
			
			responsaveis = new ArrayList<Responsaveis>();
			
			while (resultSet.next()){
				
				responsavel = new Responsaveis();
				responsavel.setIdResp(resultSet.getInt("idResp"));
				responsavel.setNomeResp(resultSet.getString("nomeResp"));
				responsavel.setCpfResp(resultSet.getString("cpfResp"));
				responsavel.setTelResp(resultSet.getString("telResp"));
				responsavel.setTel2Resp(resultSet.getString("tel2Resp"));
				responsavel.setCelResp(resultSet.getString("cel2Resp"));
				
				responsaveis.add(responsavel);
				
			}
			
		}catch(SQLException erro){
			System.out.println(erro.getMessage());
		}
		
		try {
			statement.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("\n Erro ao fechar o objeto preparedStatement.");
		}
		System.out.println("\nSucesso pesquisa");
		return responsaveis;
	}
	/* (non-Javadoc)
	 * @see rer.persistencia.IResponsaveisDAO#pesquisarResponsavel(int)
	 */
	@Override
	public Responsaveis pesquisarResponsavel(int id) {
		// TODO Auto-generated method stub
		responsavel = null;
		
		try{
			
			query = "SELECT * FROM db_ceirr.responsaveis WHERE idResp = ?";
			preparedStatement = conexao.prepareStatement(query);
			
			preparedStatement.setInt(1, id);
			
			resultSet = preparedStatement.executeQuery();
			
			if (resultSet.next()){
				
				responsavel.setIdResp(resultSet.getInt("idResp"));
				responsavel.setNomeResp(resultSet.getString("nomeResp"));
				responsavel.setCpfResp(resultSet.getString("cpfResp"));
				responsavel.setTelResp(resultSet.getString("telResp"));
				responsavel.setTel2Resp(resultSet.getString("tel2Resp"));
				responsavel.setCelResp(resultSet.getString("cel2Resp"));				
			}
			
			return responsavel;
			
		}catch(SQLException erro){
			System.out.println(erro.getMessage());
		} finally{
		
			try {
				statement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				System.out.println("\n Erro ao fechar o objeto preparedStatement.");
			}
		}
		
		System.out.println("\nSucesso pesquisa");
		
		return null;
	} 
	
	
	
	public List<Responsaveis> responsaveisAluno(int idAluno){
		
	responsavel = null;
		
		try{
			
			query = "SELECT R.* FROM db_ceirr.responsaveis R, db_ceirr.matricula M where R.Aluno_idAluno = M.Aluno_idAluno and R.Aluno_idAluno = ? and M.statusMat = 'A';";
			
			preparedStatement = conexao.prepareStatement(query);
			
			preparedStatement.setInt(1, idAluno);
			
			resultSet = preparedStatement.executeQuery();
			
			responsaveis = new ArrayList<Responsaveis>();
			
			while (resultSet.next()){
				
				responsavel = new Responsaveis();
				
				responsavel.setIdResp(resultSet.getInt("idResp"));
				responsavel.setNomeResp(resultSet.getString("nomeResp"));
				responsavel.setCpfResp(resultSet.getString("cpfResp"));
				responsavel.setTelResp(resultSet.getString("telResp"));
				responsavel.setTel2Resp(resultSet.getString("tel2Resp"));
				responsavel.setCelResp(resultSet.getString("cel2Resp"));
				
				responsaveis.add(responsavel);
				
			}
			
		}catch(SQLException erro){
			System.out.println(erro.getMessage());
		}
		
		try {
			statement.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("\n Erro ao fechar o objeto preparedStatement.");
		}
		System.out.println("\nSucesso pesquisa");
		
		return responsaveis;
		
		
	}
	
	
}
